#!/usr/bin/env python3
import argparse
import logging
import sys
import os
from datetime import datetime
from typing import Dict, List, Tuple, Set

from sqlalchemy import create_engine, MetaData, inspect
from sqlalchemy.engine.base import Engine
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.sql.elements import TextClause
from sqlalchemy.schema import DefaultClause


# ---------------------------- 日志配置 ----------------------------
def setup_logger() -> logging.Logger:
    """配置多级日志系统"""
    log_time = datetime.now().strftime("%Y%m%d_%H%M%S")
    log_file = f"schema_diff_{log_time}.log"

    logger = logging.getLogger("SchemaDiff")

    # 如果已经配置过处理器，直接返回
    if logger.handlers:
        return logger

    logger.setLevel(logging.DEBUG)

    # 文件日志（记录所有细节）
    file_handler = logging.FileHandler(log_file, encoding='utf-8')
    file_formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
    file_handler.setFormatter(file_formatter)
    logger.addHandler(file_handler)

    # 控制台日志（仅显示关键信息）
    console_handler = logging.StreamHandler()
    console_formatter = logging.Formatter('%(levelname)s: %(message)s')
    console_handler.setFormatter(console_formatter)
    # 控制台日志仅显示INFO及以上
    console_handler.setLevel(logging.WARN)  # 明确设置级别
    logger.addHandler(console_handler)

    logger.info(f"日志文件已生成: {os.path.abspath(log_file)}")
    return logger


logger = setup_logger()


# ---------------------------- 核心逻辑 ----------------------------
def log_table_diff(
        table_name: str,
        added_cols: Dict,
        removed_cols: Dict,
        modified_cols: Dict,
        added_idx: Dict,
        removed_idx: Dict,
        modified_idx: Dict
):
    """记录表级差异到日志"""
    logger = logging.getLogger("SchemaDiff")
    logger.info(f"\n===== 表结构差异: {table_name} =====")

    # 列差异
    if added_cols:
        logger.info(f"[新增列] {len(added_cols)} 个: {', '.join(added_cols.keys())}")
    if removed_cols:
        logger.info(f"[删除列] {len(removed_cols)} 个: {', '.join(removed_cols.keys())}")
    if modified_cols:
        logger.info(f"[修改列] {len(modified_cols)} 个:")
        for col, (_, changes) in modified_cols.items():
            logger.info(f"  - 列 `{col}` 修改项:")
            for change in changes:
                logger.info(f"    {change}")

    # 索引差异（详细日志）
    if added_idx:
        logger.info(f"[新增索引] {len(added_idx)} 个:")
        for name, idx in added_idx.items():
            logger.info(f"  - 索引 `{name}`: {idx['column_names']} (唯一={idx['unique']})")

    if removed_idx:
        logger.info(f"[删除索引] {len(removed_idx)} 个: {', '.join(removed_idx.keys())}")

    if modified_idx:
        logger.info(f"[修改索引] {len(modified_idx)} 个:")
        for name, idx in modified_idx.items():
            old_idx = removed_idx.get(name, {}).get('column_names', '未知')
            logger.info(f"  - 索引 `{name}`: 列 {old_idx} → {idx['column_names']}, 唯一性 {idx['unique']}")

    logger.info("=" * 50 + "\n")


def parse_args() -> argparse.Namespace:
    """解析命令行参数"""
    parser = argparse.ArgumentParser(description='MySQL数据库结构差异对比工具')
    parser.add_argument('--master', required=True, help='主库连接，格式：user:password@host/dbname')
    parser.add_argument('--target', required=True, help='目标库连接，格式同--master')
    parser.add_argument('--tables', nargs='*', default=[], help='指定对比的表列表（默认全库）')
    parser.add_argument('-o', '--output', help='输出DDL到指定文件')
    return parser.parse_args()


def create_db_engine(conn_str: str) -> Engine:
    """创建数据库引擎"""
    try:
        return create_engine(f"mysql+pymysql://{conn_str}", pool_pre_ping=True)
    except Exception as e:
        logging.getLogger("SchemaDiff").error(f"数据库连接失败: {str(e)}")
        sys.exit(1)


def reflect_table_metadata(engine: Engine, tables: List[str]) -> MetaData:
    """反射表元数据"""
    metadata = MetaData()
    try:
        metadata.reflect(bind=engine, only=tables if tables else None)
        return metadata
    except Exception as e:
        logging.getLogger("SchemaDiff").error(f"元数据反射失败: {str(e)}")
        sys.exit(1)


def normalize_default(default) -> str:
    """深度解析 SQLAlchemy 默认值对象"""
    if default is None:
        return None

    # 递归解析 DefaultClause 对象
    if isinstance(default, DefaultClause):
        return normalize_default(default.arg)

    # 处理 TextClause 对象
    if isinstance(default, TextClause):
        return default.text.strip().lower()

    # 处理字符串包裹
    str_value = str(default).strip().lower()
    if (str_value.startswith("'") and str_value.endswith("'")) or \
            (str_value.startswith('"') and str_value.endswith('"')):
        return str_value[1:-1]

    return str_value


def compare_columns(table_master, table_target) -> Tuple[Dict, Dict, Dict]:
    """对比列差异"""
    cols_master = {c.name: c for c in table_master.columns}
    cols_target = {c.name: c for c in table_target.columns}

    added = {n: cols_master[n] for n in cols_master.keys() - cols_target.keys()}
    removed = {n: cols_target[n] for n in cols_target.keys() - cols_master.keys()}
    modified = {}

    for name in cols_master.keys() & cols_target.keys():
        cm = cols_master[name]
        ct = cols_target[name]
        changes = []

        # 类型对比（规范化处理）
        type_master = str(cm.type).lower()
        type_target = str(ct.type).lower()
        if type_master != type_target:
            changes.append(f"类型: {cm.type} → {ct.type}")

        # 可为空性
        if cm.nullable != ct.nullable:
            changes.append(f"可为空: {cm.nullable} → {ct.nullable}")

        # 默认值（标准化处理）
        default_master = normalize_default(cm.server_default) if cm.server_default else None
        default_target = normalize_default(ct.server_default) if ct.server_default else None

        if default_master != default_target:
            changes.append(f"默认值: {default_master or 'NULL'} → {default_target or 'NULL'}")

        # 注释
        if cm.comment != ct.comment:
            changes.append(f"注释: '{cm.comment}' → '{ct.comment}'")

        if changes:
            modified[name] = (cm, changes)

    return added, removed, modified


def escape_sql_comment(comment: str) -> str:
    """转义注释中的特殊字符（兼容 Python 3.9+）"""
    return comment.replace("'", "''")


def generate_column_ddl(table_name: str, added: Dict, removed: Dict, modified: Dict) -> List[str]:
    """生成列DDL：处理新增、删除、修改"""
    ddl = []
    # 删除目标库多余的列
    for col_name in removed:
        ddl.append(f"ALTER TABLE `{table_name}` DROP COLUMN `{col_name}`;")

    # 新增主库有但目标库无的列
    for col_name, col in added.items():
        # 预处理默认值和注释
        default_clause = ""
        if col.server_default:
            default_clause = f"DEFAULT {col.server_default.arg}"

        comment_clause = ""
        if col.comment:
            escaped_comment = escape_sql_comment(col.comment)
            comment_clause = f"COMMENT '{escaped_comment}'"

        after_clause = f"AFTER `{list(col.table.columns)[-2].name}`" if len(col.table.columns) > 1 else ""

        # 构建完整子句
        clauses = [
            f"ADD COLUMN `{col_name}` {col.type}",
            "NOT NULL" if not col.nullable else "",
            default_clause,
            comment_clause,
            after_clause
        ]
        clauses = [c for c in clauses if c.strip()]
        ddl.append(f"ALTER TABLE `{table_name}` {' '.join(clauses)};")

    # 修改共有列
    for col_name, (col_master, changes) in modified.items():
        clauses = [f"MODIFY COLUMN `{col_name}` {col_master.type}"]

        # 处理可为空性变化
        if any(change.startswith("可为空:") for change in changes):
            clauses.append("NOT NULL" if not col_master.nullable else "NULL")

        # 处理默认值变化
        if any(change.startswith("默认值:") for change in changes) and col_master.server_default:
            clauses.append(f"DEFAULT {col_master.server_default.arg}")

        # 处理注释变化
        if any(change.startswith("注释:") for change in changes) and col_master.comment:
            escaped_comment = escape_sql_comment(col_master.comment)
            clauses.append(f"COMMENT '{escaped_comment}'")

        if len(clauses) > 1:
            ddl.append(f"ALTER TABLE `{table_name}` {' '.join(clauses)};")

    return ddl


def compare_indexes(inspector_master: Inspector, inspector_target: Inspector, table: str) -> Tuple[Dict, Dict, Dict]:
    """对比索引差异并记录到日志"""
    idx_master = {i['name']: i for i in inspector_master.get_indexes(table)}
    idx_target = {i['name']: i for i in inspector_target.get_indexes(table)}

    added = {n: idx_master[n] for n in idx_master.keys() - idx_target.keys()}
    removed = {n: idx_target[n] for n in idx_target.keys() - idx_master.keys()}
    modified = {}

    for name in idx_master.keys() & idx_target.keys():
        im = idx_master[name]
        it = idx_target[name]
        if im['column_names'] != it['column_names'] or im['unique'] != it['unique']:
            modified[name] = im

    return added, removed, modified


def generate_index_ddl(table_name: str, added: Dict, removed: Dict, modified: Dict) -> List[str]:
    """生成索引DDL并记录操作（优化日志去重）"""
    # 获取专属logger实例，避免使用root logger
    logger = logging.getLogger("SchemaDiff.DDL")

    ddl = []
    # 删除索引
    for name in removed:
        ddl.append(f"DROP INDEX `{name}` ON `{table_name}`;")
        logger.debug(f"生成删除索引语句: DROP INDEX `{name}`")  # 仅在文件中记录

    # 处理修改索引（先删旧后建新）
    for name in modified:
        ddl.append(f"DROP INDEX `{name}` ON `{table_name}`;")
        logger.debug(f"生成删除旧索引语句: DROP INDEX `{name}`")

    # 创建新索引（合并新增和修改）
    for name, idx in {**added, **modified}.items():
        unique = "UNIQUE " if idx['unique'] else ""
        columns = ", ".join([f"`{c}`" for c in idx['column_names']])
        ddl.append(f"CREATE {unique}INDEX `{name}` ON `{table_name}` ({columns});")
        logger.debug(f"生成创建索引语句: CREATE INDEX `{name}` ON {columns}")

    return ddl


def main():
    logger = setup_logger()
    args = parse_args()

    try:
        # 初始化数据库连接
        engine_master = create_db_engine(args.master)
        engine_target = create_db_engine(args.target)

        # 反射元数据
        metadata_master = reflect_table_metadata(engine_master, args.tables)
        metadata_target = reflect_table_metadata(engine_target, args.tables)
        inspector_master = inspect(engine_master)
        inspector_target = inspect(engine_target)

        # 确定对比表
        tables_master = set(metadata_master.tables.keys())
        tables_target = set(metadata_target.tables.keys())
        common_tables = (tables_master & tables_target) if not args.tables else (
                set(args.tables) & tables_master & tables_target)

        if not common_tables:
            logger.warning("无共同表需要对比")
            return

        all_ddl = []
        for tbl in common_tables:
            logger.info(f"正在处理表: {tbl}")

            # 获取表对象
            tm = metadata_master.tables[tbl]
            tt = metadata_target.tables[tbl]

            # 对比列
            added_cols, removed_cols, modified_cols = compare_columns(tm, tt)
            # 对比索引
            added_idx, removed_idx, modified_idx = compare_indexes(inspector_master, inspector_target, tbl)

            # 记录完整差异到日志
            log_table_diff(tbl, added_cols, removed_cols, modified_cols, added_idx, removed_idx, modified_idx)

            # 生成DDL
            col_ddl = generate_column_ddl(tbl, added_cols, removed_cols, modified_cols)
            idx_ddl = generate_index_ddl(tbl, added_idx, removed_idx, modified_idx)
            all_ddl.extend(col_ddl + idx_ddl)

        # 输出结果
        ddl_content = "\n".join(all_ddl)
        if args.output:
            with open(args.output, 'w', encoding='utf-8') as f:
                f.write(ddl_content)
            logger.info(f"DDL已写入文件: {args.output}")
        else:
            print("\n-- 生成的DDL语句 --\n")
            print(ddl_content)

        logger.info("数据库对比完成")

    except Exception as e:
        logger.error(f"运行出错: {str(e)}", exc_info=True)
        sys.exit(1)


if __name__ == "__main__":
    main()
